﻿using System;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Web.Services.Protocols;
using System.Xml.Linq;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.IO;
using System.IO.Packaging;
using System.Xml;
using System.Xml.XPath;
using System.Configuration;
using Microsoft.SharePoint;



[WebService(Namespace = "http://www.microsoft.com/demo/office-open-xml/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class Service : System.Web.Services.WebService
{
    public Service () 
    {    
    }

    [WebMethod]
    public string CreateSalesForecastWordDocument(int salesContactId, int forecastID, int managerID,
           int salesTerritoryId, DateTime reportDate, int pageSize, int pageNumber,
           string forecastComments, string forecastWorkbookUrl)
    {
        // The Word template to start from
        const string TEMPLATE_FILE = @"~/App_Data/ExecutiveSalesForecast.docx";
        const int MAXIMUM_QUARTERS = 8;
        string fileUrl = string.Empty;

        try
        {
            // Set up the data context and get the list of company ids.
            AdventureWorksVSTO advwrksDC = new AdventureWorksVSTO(ConfigurationManager.ConnectionStrings["AdventureWorksVSTO"].ToString());
            List<int> salesAccountIds = null;
            using (MemoryStream wordDocumentMemoryStream = new MemoryStream())
            {
                byte[] packageBytes = File.ReadAllBytes(Server.MapPath(TEMPLATE_FILE));
                wordDocumentMemoryStream.Write(packageBytes, 0, packageBytes.Length);
                using (Package wordOfficePackage =
                    Package.Open(wordDocumentMemoryStream, FileMode.Open, FileAccess.ReadWrite))
                {
                    // Get the word document part
                    PackagePart wordDocumentPart =
                        GetRootPart(wordOfficePackage,
                            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument");

                    // Get the custom XML part
                    PackagePart customXmlPart = GetSinglePart(wordOfficePackage, wordDocumentPart,
                            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/customXml");

                    // Generate the XML into the /customXml/item1.xml PackagePart by writing
                    // the new XML into the PackagePart stream.
                    GenerateCustomXml(customXmlPart.GetStream(), forecastID, salesContactId, 
                            salesTerritoryId, managerID, reportDate, forecastComments, advwrksDC, out salesAccountIds);

                    // Get information from the new custom Xml needed to bind the data
                    SortedDictionary<int, List<int>> fiscalInformation = 
                                                     GetFiscalYearInfo(customXmlPart.GetStream());

                    // Bind the Word Sales/Forecast Table Data
                    BindWordSalesTable(pageSize, MAXIMUM_QUARTERS, wordOfficePackage, wordDocumentPart, 
                                       customXmlPart, salesAccountIds, fiscalInformation);

                    // Bind the data to the Excel tables that are associated with
                    // the Word charts.
                    BindExcelChartTables(pageSize, MAXIMUM_QUARTERS, wordOfficePackage, wordDocumentPart,
                                          customXmlPart, salesAccountIds, fiscalInformation);

                    wordOfficePackage.Flush();
                    wordOfficePackage.Close();
                }

                // Get the SharePoint site and document library name
                string sharePointUrl = ConfigurationManager.AppSettings["SharePointSiteUrl"];
                string docLibSiteName = ConfigurationManager.AppSettings["DocLibSiteName"];
                string reportDocLibName = ConfigurationManager.AppSettings["SalesReportDocLibName"];
                // Load the document into SharePoint
                using (Microsoft.SharePoint.SPSite site = new SPSite(sharePointUrl))
                {
                    SPWeb web = site.AllWebs[docLibSiteName];
                    web.AllowUnsafeUpdates = true;
                    SPUser user = web.SiteUsers[HttpContext.Current.User.Identity.Name];
                    SPFolder reportFolder = web.Folders[reportDocLibName];
                    // Get the workbook title
                    fileUrl = (reportFolder.Url + forecastWorkbookUrl.Substring(forecastWorkbookUrl.LastIndexOf("/"))).ToLower();
                    fileUrl = fileUrl.Replace(".xlsx", ".docx");
                    // Get the document bytes to load into SharePoint
                    wordDocumentMemoryStream.Position = 0;
                    byte[] documentBytes = new byte[wordDocumentMemoryStream.Length];
                    wordDocumentMemoryStream.Read(documentBytes, 0, (int)wordDocumentMemoryStream.Length);
                    // Load the document into SharePoint
                    SPFile file =
                        reportFolder.Files.Add(
                            fileUrl,
                            documentBytes,
                            user,
                            user,
                            DateTime.Now,
                            DateTime.Now);
                    SPListItem item = file.Item;
                    item.SystemUpdate();
                    fileUrl = web.Url + "/" + item.Url;
                }
            }
        }
        catch (SystemException e)
        {
            fileUrl = e.Message;
            fileUrl = String.Empty;
        }

        return fileUrl;
    }

    #region "Generate XML"

    private static void GenerateCustomXml(Stream stream, int forecastID, int contactID, int territoryID, int managerID,
    DateTime forecastDate, string forecastComments, AdventureWorksVSTO advwrksDC, out List<int> salesAccountIds)
    {
        XmlWriter writerXML = XmlWriter.Create(stream);

        //</GeneralInfo>
        //  <?xml version="1.0" ?>
        writerXML.WriteStartDocument();
        //      <CompanyData>
        writerXML.WriteStartElement("CompanyData");
        //          <GeneralInfo>
        writerXML.WriteStartElement("GeneralInfo");
        //              <CompanyName>company name</CompanyName>
        writerXML.WriteElementString("CompanyName", ConfigurationManager.AppSettings["CompanyName"]);
        var territory = from terrtbl in advwrksDC.Sales_SalesTerritory
                        where terrtbl.TerritoryID == territoryID
                        select terrtbl;

        //              <TerritoryName>territory name</TerritoryName>
        writerXML.WriteElementString("TerritoryName", territory.First().Name.ToString());
        //              <TerritoryID>territory id</TerritoryID>
        writerXML.WriteElementString("TerritoryID", territoryID.ToString());
        //              <SalesManager>sales manager name</SalesManager>
        var manager = from contactTbl in advwrksDC.Person_Contact
                      join employeeTbl in advwrksDC.HumanResources_Employee
                      on contactTbl.ContactID equals employeeTbl.ContactID
                      where employeeTbl.EmployeeID == managerID
                      select contactTbl;
        writerXML.WriteElementString("SalesManager", manager.First().FirstName + " " + manager.First().LastName);
        var contact = from contactTbl in advwrksDC.Person_Contact
                      where contactTbl.ContactID == contactID
                      select contactTbl;
        //              <SalesPerson>sales person name</SalesPerson>
        writerXML.WriteElementString("SalesPerson", contact.First().FirstName + " " + contact.First().LastName);
        //              <SalesPersonID>sales person id</SalesPersonID>
        writerXML.WriteElementString("SalesPersonID", contactID.ToString());
        //              <CurrentDate>current date</CurrentDate>
        writerXML.WriteElementString("CurrentDate", DateTime.Today.ToString("s"));
        //              <ForecastComments>comments</ForecastComments>
        writerXML.WriteElementString("ForecastComments", forecastComments);
        //          </GeneralInfo>
        writerXML.WriteEndElement();
        //          <Sales>
        writerXML.WriteStartElement("Sales");

        // Write out the customer data
        GenerateCustomerXml(writerXML, forecastID, forecastDate, advwrksDC, out salesAccountIds);

        //          </Sales>
        writerXML.WriteEndElement();
        //      </CompanyData>
        writerXML.WriteEndElement();
        writerXML.WriteEndDocument();

        writerXML.Flush();

        return;
    }

    /// <summary>
    /// Generate the XML the sales and forecast for a customer identified by fiscal years
    /// and quarters
    /// </summary>
    /// <param name="writer">XmlWriter</param>
    private static void GenerateCustomerXml(XmlWriter writerXML, int forecastID, DateTime forecastDate, AdventureWorksVSTO advwrksDC, out List<int> salesAccountIds)
    {
        salesAccountIds = new List<int>();

        var salesForecast = from forecastTbl in advwrksDC.Sales_Forecast
                            where forecastTbl.ForecastID == forecastID
                            select forecastTbl;
        int employeeID = salesForecast.First().ForecastSalesPersonID;
        var salesForecastQuery = from forecastestTbl in advwrksDC.Sales_ForecastEstimates
                                 where forecastestTbl.ForeCastID == forecastID
                                 select forecastestTbl;
        int lastCustomer = 0;
        int customerCount = 0;
        foreach (var forecast in salesForecastQuery)
        {
            int currentCustomer = forecast.CompanyID;
            if (lastCustomer != currentCustomer)
            {
                customerCount++;
                if (lastCustomer != 0)
                {
                    //  </Customer>
                    writerXML.WriteEndElement();
                }

                //  <Customer>
                writerXML.WriteStartElement("Customer");
                //      <Name>customer name</Name>
                var customer = from customerTbl in advwrksDC.Sales_Customer
                               where customerTbl.CustomerID == currentCustomer
                               select customerTbl;
                string custName = customer.First().CustomerName;
                if (custName.Length > 11) custName = custName.Substring(0, 11);
                writerXML.WriteElementString("Name", custName);
                //      <CustomerID>customer id</CustomerID>
                writerXML.WriteElementString("CustomerID", currentCustomer.ToString());
                salesAccountIds.Add(currentCustomer);

                GenerateQuarterXml(writerXML, forecastID, employeeID, currentCustomer, forecastDate, advwrksDC);

                lastCustomer = currentCustomer;
            }
        }
        //   </Customer>
        writerXML.WriteEndElement();
        if ( customerCount < 5)
        {
            //Need placeholder data...
            //  <Customer>
            writerXML.WriteStartElement("Customer");
            //      <Name>customer name</Name>
            writerXML.WriteElementString("Name", "-");
            //      <CustomerID>customer id</CustomerID>
            writerXML.WriteElementString("CustomerID", Convert.ToString(0));
            for (int i = customerCount; i < 5; i++)
            {
                // fill in the remaining customer id's as customer 0 so that
                // customer 0 will be used to fill in the remaining slots in the
                // template.
                salesAccountIds.Add(0);
            }
            int qtr = 0;
            string year = "2008";
            ForecastData data;
            Dictionary<int, ForecastData> QuarterlySales = new Dictionary<int, ForecastData>(4);
            // unlike forecasts, we only need to compute the start year for the fiscal
            // information and then build the 4 quarters
            qtr = GetFYKey(forecastDate.Month, forecastDate.Year, out year) + 1;
            if (qtr == 5) qtr = 1;
            for (int i = 1; i < 5; i++)
            {
                data = new ForecastData();
                data.Sales = 0;
                data.Probability = 0;
                data.Quarter = i;
                if (i >= qtr)
                {
                    data.FiscalYear = year;
                }
                else
                {
                    data.FiscalYear = (Convert.ToInt16(year) + 1).ToString();
                }
                QuarterlySales.Add(i, data);
            }
            for (qtr = 1; qtr < 5; qtr++)
            {
                //      <FiscalYear>
                writerXML.WriteStartElement("FiscalYear");
                //          <Name>fiscal year name</Name>
                writerXML.WriteElementString("Name", QuarterlySales[qtr].FiscalYear);
                //          <Quarters>
                writerXML.WriteStartElement("Quarters");
                //  <Quarter>
                writerXML.WriteStartElement("Quarter");
                //      <Name>quarter name</Name>
                writerXML.WriteElementString("Name", QuarterlySales[qtr].Quarter.ToString());
                //      <ActualSales>0.00</ActualSales>
                writerXML.WriteElementString("ActualSales", Decimal.Zero.ToString("C"));
                //      <RawForecast>0.00</RawForecast>
                writerXML.WriteElementString("RawForecast", Decimal.Zero.ToString("C"));
                //      <Probability>.50</Probability>
                writerXML.WriteElementString("Probability", Decimal.Zero.ToString("P"));
                //      <AdjustedForecast>0.00</AdjustedForecast>
                writerXML.WriteElementString("AdjustedForecast", Decimal.Zero.ToString("C"));
                //      <Variance>0.00</Variance>
                writerXML.WriteElementString("Variance", Decimal.Zero.ToString("C"));
                //  </Quarter>
                writerXML.WriteEndElement();

                // </Quarters>
                writerXML.WriteEndElement();
                // </FiscalYear>
                writerXML.WriteEndElement();
            }

            for (qtr = 1; qtr < 5; qtr++)
            {
                //      <FiscalYear>
                writerXML.WriteStartElement("FiscalYear");
                //          <Name>fiscal year name</Name>
                int fy = Convert.ToInt32(QuarterlySales[qtr].FiscalYear);
                fy++;
                writerXML.WriteElementString("Name", fy.ToString());
                //          <Quarters>
                writerXML.WriteStartElement("Quarters");
                //  <Quarter>
                writerXML.WriteStartElement("Quarter");
                //      <Name>quarter name</Name>
                writerXML.WriteElementString("Name", QuarterlySales[qtr].Quarter.ToString());
                //      <ActualSales>0.00</ActualSales>
                writerXML.WriteElementString("ActualSales", Decimal.Zero.ToString("C"));
                //      <RawForecast>0.00</RawForecast>
                writerXML.WriteElementString("RawForecast", Decimal.Zero.ToString("C"));
                //      <Probability>.50</Probability>
                writerXML.WriteElementString("Probability", Decimal.Zero.ToString("P"));
                //      <AdjustedForecast>0.00</AdjustedForecast>
                writerXML.WriteElementString("AdjustedForecast", Decimal.Zero.ToString("C"));
                //      <Variance>0.00</Variance>
                writerXML.WriteElementString("Variance", Decimal.Zero.ToString("C"));
                //  </Quarter>
                writerXML.WriteEndElement();

                // </Quarters>
                writerXML.WriteEndElement();
                // </FiscalYear>
                writerXML.WriteEndElement();
            }
            //   </Customer>
            writerXML.WriteEndElement();
        }
        return;
    }

    /// <summary>
    /// Generate the XML for a company's Fiscal Year by Quarter
    /// </summary>
    private static void GenerateQuarterXml(XmlWriter writerXML, int forecastID, int SalesEmployeeID, int customerID, DateTime forecastDate, AdventureWorksVSTO advwrksDC)
    {
        int qtr = 0;
        string year = "2008";
        ForecastData data;
        Dictionary<int, ForecastData> QuarterlySales = new Dictionary<int, ForecastData>(4);
        // unlike forecasts, we only need to compute the start year for the fiscal
        // information and then build the 4 quarters
        qtr = GetFYKey(forecastDate.Month, forecastDate.Year, out year);
        for (int i = 1; i < 5; i++)
        {
            data = new ForecastData();
            data.Sales = 0;
            data.Probability = 0;
            data.Quarter = i;
            if (i <= qtr)
            {
                data.FiscalYear = year;
            }
            else
            {
                data.FiscalYear = (Convert.ToInt16(year) - 1).ToString();
            }
            QuarterlySales.Add(i, data);
        }

        //Need to ensure we get 4 quarters... may only get three - in which case need a 4th...
        var qtrSales = advwrksDC.UspGetSalesDataByFiscalQuarters(SalesEmployeeID, customerID, forecastDate);
        foreach (UspGetSalesDataByFiscalQuartersResult actSales in qtrSales)
        {
            // Populate the appropriate quarters.  Note that in a demo scenario this can return more
            // then 4 quarters worth of data, which is why we have to map the quarters to the correct data.
            qtr = actSales.FiscalQuarter.Value;
            if (QuarterlySales[qtr].FiscalYear == actSales.FiscalYear.ToString())
            {
                data = QuarterlySales[qtr];
                data.Sales = actSales.ActualSales.Value;
                QuarterlySales[qtr] = data;
                //QuarterlySales[qtr].Sales = actSales.ActualSales.Value;
            }
        }
        for (qtr = 1; qtr < 5; qtr++)
        {
            //      <FiscalYear>
            writerXML.WriteStartElement("FiscalYear");
            //          <Name>fiscal year name</Name>
            writerXML.WriteElementString("Name", QuarterlySales[qtr].FiscalYear);
            //          <Quarters>
            writerXML.WriteStartElement("Quarters");

            //  <Quarter>
            writerXML.WriteStartElement("Quarter");
            //      <Name>quarter name</Name>
            writerXML.WriteElementString("Name", QuarterlySales[qtr].Quarter.ToString());
            //      <ActualSales>0.00</ActualSales>
            writerXML.WriteElementString("ActualSales", QuarterlySales[qtr].Sales.ToString("C"));
            //      <RawForecast>0.00</RawForecast>
            writerXML.WriteElementString("RawForecast", Decimal.Zero.ToString("C"));
            //      <Probability>.50</Probability>
            writerXML.WriteElementString("Probability", Decimal.Zero.ToString("P"));
            //      <AdjustedForecast>0.00</AdjustedForecast>
            writerXML.WriteElementString("AdjustedForecast", Decimal.Zero.ToString("C"));
            //      <Variance>0.00</Variance>
            writerXML.WriteElementString("Variance", Decimal.Zero.ToString("C"));
            //  </Quarter>
            writerXML.WriteEndElement();

            // </Quarters>
            writerXML.WriteEndElement();
            // </FiscalYear>
            writerXML.WriteEndElement();
        }

        Dictionary<int, ForecastData> QuarterlyForecasts = new Dictionary<int, ForecastData>(4);
        var salesforecast = from forecastEstTbl in advwrksDC.Sales_ForecastEstimates
                                where forecastEstTbl.CompanyID == customerID && forecastEstTbl.ForeCastID == forecastID
                                select forecastEstTbl;
        foreach (var salesforecastrow in salesforecast)
        {
            qtr = GetFYKey(salesforecastrow.Month, salesforecastrow.Year, out year);
            if (QuarterlyForecasts.ContainsKey(qtr))
            {
                // If the quarter has been started continue to add the 
                //values (reduce the total percentage by 3 when used.)
                data = QuarterlyForecasts[qtr];
                data.Probability += salesforecastrow.Confidence;
                data.Sales += salesforecastrow.SalesForecast;
                QuarterlyForecasts[qtr] = data;
            }
            else
            {
                // First entry for a new forecast - create a new entry.
                data = new ForecastData();
                data.FiscalYear = year;
                data.Quarter = qtr;
                data.Probability = salesforecastrow.Confidence;
                data.Sales = salesforecastrow.SalesForecast;
                QuarterlyForecasts.Add(qtr, data);
            }
        }
        for (qtr = 1; qtr<5; qtr++)
        {
            // Now get the forecast for the matching quarters...
            //      <FiscalYear>
            writerXML.WriteStartElement("FiscalYear");
            //          <Name>fiscal year name</Name>
            writerXML.WriteElementString("Name", QuarterlyForecasts[qtr].FiscalYear);
            //          <Quarters>
            writerXML.WriteStartElement("Quarters");

            // A quarter's worth of data...
            //  <Quarter>
            writerXML.WriteStartElement("Quarter");
            //      <Name>quarter name</Name>
            writerXML.WriteElementString("Name", QuarterlyForecasts[qtr].Quarter.ToString());
            //      <ActualSales>0.00</ActualSales>
            writerXML.WriteElementString("ActualSales", Decimal.Zero.ToString("C"));
            //      <RawForecast>0.00</RawForecast>
            writerXML.WriteElementString("RawForecast", QuarterlyForecasts[qtr].Sales.ToString("C"));
            //      <Probability>.50</Probability>
            writerXML.WriteElementString("Probability", (QuarterlyForecasts[qtr].Probability / 3).ToString("P"));
            //      <AdjustedForecast>0.00</AdjustedForecast>
            writerXML.WriteElementString("AdjustedForecast", (QuarterlyForecasts[qtr].Sales * ((QuarterlyForecasts[qtr].Probability / 3) / 100)).ToString("C"));
            //      <Variance>0.00</Variance>
            //if (actualSales > Decimal.Zero)
            //{
            //    variance = actualSales - rawForecast;
            //}
            writerXML.WriteElementString("Variance", Decimal.Zero.ToString("C"));
            //  </Quarter>
            writerXML.WriteEndElement();

            // </Quarters>
            writerXML.WriteEndElement();
            // </FiscalYear>
            writerXML.WriteEndElement();
        }
        return;
    }

    private static int GetFYKey(int month, int year, out string fyyear)
    {
        int qtr = 1;
        fyyear = "2009";
        switch (month)
        {
            case 1:
            case 2:
            case 3:
                fyyear = (year).ToString();
                qtr = 3;
                break;
            case 4:
            case 5:
            case 6:
                fyyear = (year).ToString();
                qtr = 4;
                break;
            case 7:
            case 9:
            case 8:
                fyyear = (year + 1).ToString();
                qtr = 1;
                break;
            case 10:
            case 11:
            case 12:
                fyyear = (year + 1).ToString();
                qtr = 2;
                break;
            default:
                break;
        }
        return qtr;
    }

    #endregion

    #region "Get Fiscal Year Info"
    /// <summary>
        /// Get the information sorted by fiscal year and then quarter as to what fiscal years
        /// and fiscal quarters are in the data.
        /// </summary>
        /// <param name="stream">Stream</param>
        /// <returns>SortedDictionary&lt;int, List&lt;int&gt;&gt;</returns>
        private static SortedDictionary<int, List<int>> GetFiscalYearInfo(Stream stream)
        {
            // Pass an XmlTextReader that we don't close to the XPathDocument 
            // so it doesn't close the stream
            stream.Position = 0;
            XmlTextReader reader = new XmlTextReader(stream);
            XPathDocument document = new XPathDocument(reader);
            XPathNavigator navigator = document.CreateNavigator();
            XPathNodeIterator fiscalYears =
                navigator.SelectDescendants("FiscalYear", string.Empty, false);
            // Figure out what fiscal years and quarters we have to display.  
            // SortedDictionary<FiscalYear, SortedList<FiscalYearQuarter>>
            SortedDictionary<int, List<int>> fiscalInformation = new SortedDictionary<int, List<int>>();
            foreach (XPathNavigator fiscalYearNavigator in fiscalYears)
            {
                // Get the fiscal year number
                fiscalYearNavigator.MoveToFirstChild();
                int fiscalYear = fiscalYearNavigator.ValueAsInt;
                // Get the fiscal year quarters numbers
                fiscalYearNavigator.MoveToNext();
                XPathNodeIterator quarters =
                    fiscalYearNavigator.SelectDescendants("Quarter", string.Empty, false);
                foreach (XPathNavigator quarterNavigator in quarters)
                {
                    // Get the quarter number
                    quarterNavigator.MoveToFirstChild();
                    int fiscalQuarter = quarterNavigator.ValueAsInt;

                    // Add the set to the fiscal information
                    if (fiscalInformation.ContainsKey(fiscalYear))
                    {
                        if (!fiscalInformation[fiscalYear].Contains(fiscalQuarter))
                        {
                            fiscalInformation[fiscalYear].Add(fiscalQuarter);
                        }
                    }
                    else
                    {
                        List<int> quarterList = new List<int>();
                        quarterList.Add(fiscalQuarter);
                        fiscalInformation.Add(fiscalYear, quarterList);
                    }
                }
            }

            // Sort the quarters for each fiscal year
            foreach (KeyValuePair<int, List<int>> fiscalYearValue in fiscalInformation)
            {
                fiscalYearValue.Value.Sort();
            }

            return fiscalInformation;
        }
        #endregion

    #region "Bind Word Table Values"
        /// <summary>
        /// Bind the information to the Word sales table
        /// </summary>
        /// <param name="pageSize">int</param>
        /// <param name="maximumQuarters">int</param>
        /// <param name="officePackage">Package</param>
        /// <param name="officeDocumentPart">PackagePart</param>
        /// <param name="customXmlPart">PackagePart</param>
        /// <param name="salesAccountIds">List&lt;int&gt;</param>
        /// <param name="fiscalInformation">SortedDictionary&lt;int, List&lt;int&gt;&gt;</param>
        private static void BindWordSalesTable(int pageSize, int maximumQuarters,
            Package officePackage, PackagePart officeDocumentPart,
            PackagePart customXmlPart, List<int> salesAccountIds, SortedDictionary<int, List<int>> fiscalInformation)
        {
            // Generate the data needed to bind the content areas
            Dictionary<string, string> contentAreaBindings =
                GenerateContentAreaBindings(
                    customXmlPart.GetStream(),
                    pageSize,
                    maximumQuarters,
                    salesAccountIds,
                    fiscalInformation);

            // Get the custom XML datastore id so we can bind the content areas
            string datastoreId =
                GetCustomXmlDatastoreId(officePackage, customXmlPart);

            // Bind the contents of the content areas
            XmlNamespaceManager nsMgr = new XmlNamespaceManager(new NameTable());
            nsMgr.AddNamespace("w", "http://schemas.openxmlformats.org/wordprocessingml/2006/main");
            XmlDocument document = new XmlDocument();
            document.Load(officeDocumentPart.GetStream());
            XPathNavigator navigator = document.CreateNavigator();
            XmlDocument dataDocument = new XmlDocument();
            dataDocument.Load(customXmlPart.GetStream());
            foreach (KeyValuePair<string, string> bindingsValue in contentAreaBindings)
            {
                // If this is an xpath figure out if a value exists at the specified path
                string dataValue = bindingsValue.Value;
                bool isXPathBinding = (dataValue[0] != '@');
                if (isXPathBinding)
                {
                    XmlNode dataNode = dataDocument.SelectSingleNode(dataValue);
                    if (dataNode == null)
                    {
                        isXPathBinding = false;
                        dataValue = string.Format("@{0}", 0.ToString("C"));
                    }
                }

                if (isXPathBinding)
                {
                    SetContentAreaXmlMapping(nsMgr, navigator, datastoreId, bindingsValue.Key, dataValue);
                }
                else
                {
                    SetContentAreaContent(nsMgr, navigator, bindingsValue.Key, dataValue);
                }
            }

            document.Save(officeDocumentPart.GetStream(FileMode.Create, FileAccess.Write));

            return;
        }

        /// <summary>
        /// Generate the content area bindings for the content areas in the Word document.
        /// If the value of a binding starts with '@' then it is a literal value meant to be
        /// in the <w:sdtContent> rather than an Xml binding meant to be in <w:sdtPr>
        /// </summary>
        /// <param name="stream">Stream</param>
        /// <param name="pageSize">int</param>
        /// <param name="salesAccountIds">List&lt;string&gt;</param>
        /// <param name="fiscalInformation">SortedDictionary&lt;int, List&lt;int&gt;&gt;</param>
        /// <returns>Dictionary&lt;string, string&gt;</returns>
        private static Dictionary<string, string> GenerateContentAreaBindings(Stream stream, int pageSize,
            int maximumQuarters, List<int> salesAccountIds, SortedDictionary<int, List<int>> fiscalInformation)
        {
            // Dictionary<ContentAreaName, XmlBindingPath>
            Dictionary<string, string> contentAreaBindings = new Dictionary<string, string>();
            // Bind the header information
            contentAreaBindings.Add("CompanyNameHeader", "/CompanyData/GeneralInfo/CompanyName");
            contentAreaBindings.Add("TerritoryHeader", "/CompanyData/GeneralInfo/TerritoryName");
            contentAreaBindings.Add("SalesManagerHeader", "/CompanyData/GeneralInfo/SalesManager");
            contentAreaBindings.Add("SalesPersonHeader", "/CompanyData/GeneralInfo/SalesPerson");
            contentAreaBindings.Add("CurrentDateHeader", "/CompanyData/GeneralInfo/CurrentDate");
            contentAreaBindings.Add("Comments", "/CompanyData/GeneralInfo/ForecastComments");

            // Loop through the row numbers and set the binding paths for each row header (customer name)
            for (int rowNumber = 0; rowNumber < pageSize; rowNumber++)
            {
                contentAreaBindings.Add(
                    string.Format("R{0}Title", (rowNumber + 1).ToString()),
                    string.Format(
                        "/CompanyData/Sales/Customer[CustomerID={0}]/Name",
                        salesAccountIds[rowNumber].ToString()));
            }

            // Get the information as to what fiscal years and quarters exist in the data
            int quarterCount = 0;
            string startFiscalRange = string.Empty;
            string endFiscalRange = string.Empty;
            foreach (KeyValuePair<int, List<int>> fiscalYearValue in fiscalInformation)
            {
                foreach (int fiscalQuarter in fiscalYearValue.Value)
                {
                    quarterCount++;

                    string columnHeader =
                        string.Format(
                            "@FY{0} Q{1}",
                            fiscalYearValue.Key.ToString(),
                            fiscalQuarter.ToString());
                    // If this is the first quarter encountered then save the value 
                    if (quarterCount == 1)
                    {
                        startFiscalRange = columnHeader;
                    }

                    // Add the column title.  '@' at the beginning of the beginning of the value
                    // means that it's the Content Area value rather than an XPath.
                    contentAreaBindings.Add(
                        string.Format("Q{0}Title", quarterCount.ToString()),
                        columnHeader);

                    // If the quarter is one of the first four quarters then it needs to get it's
                    // values from the 'ActualSales' value otherwise if the quarter is one of the 
                    // last four quarters then it needs to get it's values from the 'RawForecast'
                    // value
                    string salesForecastNodeName;
                    if (quarterCount <= 4)
                    {
                        salesForecastNodeName = "ActualSales";
                    }
                    else
                    {
                        salesForecastNodeName = "RawForecast";
                    }

                    // Loop through the row numbers and set the binding paths for each row in
                    // the column.
                    for (int rowNumber = 0; rowNumber < pageSize; rowNumber++)
                    {
                        contentAreaBindings.Add(
                            string.Format("R{0}Q{1}", (rowNumber + 1).ToString(), quarterCount.ToString()),
                            string.Format(
                                "/CompanyData/Sales/Customer[CustomerID={0}]/" +
                                "FiscalYear[Name={1}]/Quarters/Quarter[Name={2}]/{3}",
                                salesAccountIds[rowNumber].ToString(),
                                fiscalYearValue.Key.ToString(),
                                fiscalQuarter.ToString(),
                                salesForecastNodeName));
                    }

                    // Set this every iteration.  When the processing is done then it will be set to
                    // the last one that was processed.
                    endFiscalRange = columnHeader;

                    if (quarterCount == maximumQuarters)
                    {
                        break;
                    }
                }

                if (quarterCount == maximumQuarters)
                {
                    break;
                }
            }

            // Add the fiscal range content area mappings
            contentAreaBindings.Add("StartFiscalRangeHeader", startFiscalRange);
            contentAreaBindings.Add("EndFiscalRangeHeader", endFiscalRange);

            return contentAreaBindings;
        }
        #endregion

    #region "Bind Excel Table Values"
        /// <summary>
        /// Fix up the Excel tables that are associated with the charts in the 
        /// Word document.
        /// </summary>
        /// <param name="pageSize">int</param>
        /// <param name="maximumQuarters">int</param>
        /// <param name="wordOfficePackage">Package</param>
        /// <param name="wordDocumentPart">PackagePart</param>
        /// <param name="customXmlPart">PackagePart</param>
        /// <param name="salesAccountIds">List&lt;string&gt;</param>
        /// <param name="fiscalInformation">SortedDictionary&lt;int, List&lt;int&gt;&gt;</param>
        private static void BindExcelChartTables(int pageSize, int maximumQuarters,
            Package wordOfficePackage, PackagePart wordDocumentPart, PackagePart customXmlPart,
            List<int> salesAccountIds, SortedDictionary<int, List<int>> fiscalInformation)
        {
            // Get the chart relationships
            PackageRelationshipCollection chartRelationships =
                wordDocumentPart.GetRelationshipsByType(
                    "http://schemas.openxmlformats.org/officeDocument/2006/relationships/chart");
            foreach (PackageRelationship chartRelationship in chartRelationships)
            {
                // Get the chart part
                PackagePart chartPart = ResolvePartRelationship(wordOfficePackage, chartRelationship);
                // Get the PackagePart for the embedded Excel document
                PackagePart excelPart =
                    GetSinglePart(
                        wordOfficePackage,
                        chartPart,
                        "http://schemas.openxmlformats.org/officeDocument/2006/relationships/package");
                // Load the Excel package
                using (Package excelOfficePackage =
                    Package.Open(excelPart.GetStream(), FileMode.Open, FileAccess.ReadWrite))
                {
                    // Get the excel document part
                    PackagePart excelDocumentPart =
                        GetRootPart(
                            excelOfficePackage,
                            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument");
                    // Get the worksheet part
                    PackagePart worksheetPart =
                        GetSinglePart(
                            excelOfficePackage,
                            excelDocumentPart,
                            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/worksheet");
                    // Get the table part
                    PackagePart tablePart =
                        GetSinglePart(
                            excelOfficePackage,
                            worksheetPart,
                            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/table");
                    // Get the shared strings part
                    PackagePart sharedStringsPart =
                        GetSinglePart(
                            excelOfficePackage,
                            excelDocumentPart,
                            "http://schemas.openxmlformats.org/officeDocument/2006/relationships/sharedStrings");

                    // Populate the appropriate chart data
                    PopulateChartData(chartPart, pageSize, maximumQuarters,
                        worksheetPart, tablePart, sharedStringsPart, customXmlPart,
                        salesAccountIds, fiscalInformation);

                    // Save the excel data
                    excelOfficePackage.Flush();
                    excelOfficePackage.Close();
                }
            }

            return;
        }

        /// <summary>
        /// Populate the data for the chart
        /// </summary>
        /// <param name="chartPart">PackagePart</param>
        /// <param name="pageSize">int</param>
        /// <param name="maximumQuarters">int</param>
        /// <param name="worksheetPart">PackagePart</param>
        /// <param name="tablePart">PackagePart</param>
        /// <param name="sharedStringsPart">PackagePart</param>
        /// <param name="customXmlPart">PackagePart</param>
        /// <param name="salesAccountIds">List&lt;string&gt;</param>
        /// <param name="fiscalInformation">SortedDictionary&lt;int, List&lt;int&gt;&gt;</param>
        private static void PopulateChartData(PackagePart chartPart, int pageSize, int maximumQuarters,
            PackagePart worksheetPart, PackagePart tablePart, PackagePart sharedStringsPart, PackagePart customXmlPart,
            List<int> salesAccountIds, SortedDictionary<int, List<int>> fiscalInformation)
        {
            // We are assuming that salesAccountIds has pageSize elements in it

            XmlNamespaceManager nsMgr = new XmlNamespaceManager(new NameTable());
            nsMgr.AddNamespace("ss", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
            nsMgr.AddNamespace("c", "http://schemas.openxmlformats.org/drawingml/2006/chart");
            // Create the worksheet navigator
            XmlDocument worksheetDocument = new XmlDocument();
            worksheetDocument.Load(worksheetPart.GetStream());
            XPathNavigator worksheetNavigator = worksheetDocument.CreateNavigator();
            // Create the shared strings navigator
            XmlDocument sharedStringsDocument = new XmlDocument();
            sharedStringsDocument.Load(sharedStringsPart.GetStream());
            XPathNavigator sharedStringsNavigator = sharedStringsDocument.CreateNavigator();
            // Create the table navigator
            XmlDocument tableDocument = new XmlDocument();
            tableDocument.Load(tablePart.GetStream());
            XPathNavigator tableNavigator = tableDocument.CreateNavigator();
            // Create the chart navigator
            XmlDocument chartDocument = new XmlDocument();
            chartDocument.Load(chartPart.GetStream());
            XPathNavigator chartNavigator = chartDocument.CreateNavigator();
            // Create the custom xml navigator.  Use an XmlTextReader so XPathDocument doesn't 
            XmlTextReader reader = new XmlTextReader(customXmlPart.GetStream());
            XPathDocument customXmlDocument = new XPathDocument(reader);
            XPathNavigator customXmlNavigator = customXmlDocument.CreateNavigator();

            // Populate the column headers which are the legend.  The column headers
            // start in column 2 and extend out to pageSize + 1.
            const int COL_HEADER_ROW_NUMBER = 1;
            const int ROW_HEADER_COL_NUMBER = 1;
            for (int colNumber = 2; colNumber <= (pageSize + 1); colNumber++)
            {
                // Get the customer account number.  The customer will definitely
                // exist so no need to check to make sure we found it.
                string dataPath =
                    string.Format(
                        "/CompanyData/Sales/Customer[CustomerID={0}]/Name",
                        salesAccountIds[colNumber - 2].ToString());
                XPathNavigator dataValueNavigator = customXmlNavigator.SelectSingleNode(dataPath);

                // Set the value into the shared strings
                SetCellValue(
                    nsMgr,
                    worksheetNavigator,
                    sharedStringsNavigator,
                    COL_HEADER_ROW_NUMBER,
                    colNumber,
                    dataValueNavigator.Value);

                // Update the tablePart
                XPathNavigator tableColumnNavigator =
                    tableNavigator.SelectSingleNode(
                        string.Format(
                            "//ss:tableColumn[position()={0}]",
                            colNumber.ToString()),
                        nsMgr);
                tableColumnNavigator.MoveToAttribute("name", string.Empty);
                tableColumnNavigator.SetValue(dataValueNavigator.Value);

                // Update the chartPart
                XPathNavigator chartHeaderNavigator =
                    chartNavigator.SelectSingleNode(
                        string.Format(
                            "/c:chartSpace/c:chart/c:plotArea//c:ser[position()={0}]/c:tx/c:strRef/c:strCache/c:pt/c:v",
                            (colNumber - 1).ToString()),
                        nsMgr);
                chartHeaderNavigator.SetValue(dataValueNavigator.Value);
            }

            // Populate the row headers and the data
            int quarterCount = 0;
            int rowNumber = 2;
            bool isChart1 = chartPart.Uri.ToString().Equals("/word/charts/chart1.xml");
            foreach (KeyValuePair<int, List<int>> fiscalYearValue in fiscalInformation)
            {
                foreach (int fiscalQuarter in fiscalYearValue.Value)
                {
                    quarterCount++;

                    // Set the row header
                    string rowHeader =
                        string.Format(
                            "FY{0} Q{1}",
                            fiscalYearValue.Key.ToString(),
                            fiscalQuarter.ToString());
                    SetCellValue(nsMgr, worksheetNavigator, sharedStringsNavigator,
                        rowNumber, ROW_HEADER_COL_NUMBER, rowHeader);

                    // Set the chart row headers
                    XPathNodeIterator chartRowHeaders =
                        chartNavigator.Select(
                            string.Format(
                                "/c:chartSpace/c:chart/c:plotArea//c:ser/c:cat/c:strRef/c:strCache/c:pt[position()={0}]/c:v",
                                (rowNumber - 1).ToString()),
                            nsMgr);
                    foreach (XPathNavigator rowHeaderNavigator in chartRowHeaders)
                    {
                        rowHeaderNavigator.SetValue(rowHeader);
                    }


                    // Figure out the XPath to the appropriate value for the appropriate chart
                    string dataNode = string.Empty;
                    if (isChart1)
                    {
                        // Set the path for the Actual Sales chart data
                        if (quarterCount <= 4)
                        {
                            dataNode = "ActualSales";
                        }
                        else
                        {
                            dataNode = "RawForecast";
                        }
                    }
                    else
                    {
                        // Set the path for the Sales Forecast chart data
                        dataNode = "Probability";
                    }

                    // Set the data values
                    for (int colNumber = 2; colNumber <= (pageSize + 1); colNumber++)
                    {
                        // Create the xpath and get the value using it from the custom xml
                        string dataPath =
                            string.Format(
                                "/CompanyData/Sales/Customer[CustomerID={0}]" +
                                "/FiscalYear[Name={1}]/Quarters/Quarter[Name={2}]" +
                                "/{3}",
                                salesAccountIds[colNumber - 2].ToString(),
                                fiscalYearValue.Key.ToString(),
                                fiscalQuarter.ToString(),
                                dataNode);
                        XPathNavigator dataValueNavigator = customXmlNavigator.SelectSingleNode(dataPath);
                        // Both of the charts are decimal values so default to zero
                        decimal numericValue = 0;
                        string stringValue = "0";
                        if (dataValueNavigator != null)
                        {
                            stringValue = dataValueNavigator.Value;
                            if (stringValue.EndsWith("%"))
                            {
                                stringValue = stringValue.Substring(0, stringValue.Length - 2);
                                numericValue = decimal.Parse(stringValue) / 100;
                            }
                            else
                            {
                                numericValue = decimal.Parse(stringValue, NumberStyles.Currency);
                            }

                            stringValue = numericValue.ToString();
                        }

                        // Set the data value into the cell
                        SetCellValue(
                            nsMgr,
                            worksheetNavigator,
                            sharedStringsNavigator,
                            rowNumber,
                            colNumber,
                            stringValue);

                        // Set the chart point values
                        chartNavigator.MoveToRoot();
                        XPathNavigator chartDataPoint =
                            chartNavigator.SelectSingleNode(
                                string.Format(
                                    "/c:chartSpace/c:chart/c:plotArea//c:ser[position()={0}]/c:val/c:numRef/c:numCache/c:pt[position()={1}]/c:v",
                                    (colNumber - 1).ToString(),
                                    (rowNumber - 1).ToString()),
                                nsMgr);
                        chartDataPoint.SetValue(stringValue);
                    }

                    if (quarterCount == maximumQuarters)
                    {
                        break;
                    }

                    rowNumber++;
                }

                if (quarterCount == maximumQuarters)
                {
                    break;
                }
            }

            // Save the shared strings and the sheet
            sharedStringsDocument.Save(sharedStringsPart.GetStream(FileMode.Create, FileAccess.Write));
            tableDocument.Save(tablePart.GetStream(FileMode.Create, FileAccess.Write));
            worksheetDocument.Save(worksheetPart.GetStream(FileMode.Create, FileAccess.Write));
            chartDocument.Save(chartPart.GetStream(FileMode.Create, FileAccess.Write));

            return;
        }

        /// <summary>
        /// Set a cell value.  Only supports simple numeric text strings and shared
        /// strings that are simple text strings.  Both row and col number
        /// are 1 based not zero based.  In the simple documents in the template
        /// all shared strings are row or column headers, all else is numeric so if
        /// it's already a shared string then we don't change that or if it isn't
        /// a shared string then the value must be numeric.
        /// </summary>
        /// <param name="nsMgr">XmlNamespaceManager</param>
        /// <param name="worksheetNavigator">XPathNavigator</param>
        /// <param name="sharedStringsNavigator">XPathNavigator</param>
        /// <param name="row">int</param>
        /// <param name="col">int</param>
        /// <param name="value">string</param>
        private static void SetCellValue(XmlNamespaceManager nsMgr,
            XPathNavigator worksheetNavigator, XPathNavigator sharedStringsNavigator,
            int row, int col, string value)
        {
            // Navigate to the correct cell.  Use a simple algorithm to get to the cell
            // rather than calculate the column designation such as 'A1' or 'F1'.
            XPathNavigator cellNavigator =
                worksheetNavigator.SelectSingleNode(
                    string.Format(
                        "//ss:sheetData/ss:row[@r={0}]/ss:c[@r='{1}']",
                        row.ToString(),
                        GetCellAddress(row, col)),
                    nsMgr);

            // Check if this cell is using a shared string
            if (cellNavigator.MoveToAttribute("t", string.Empty) &&
                cellNavigator.Value == "s")
            {
                // Get the share string index
                cellNavigator.MoveToParent();
                cellNavigator.MoveToChild("v", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                int sharedStringIndex = cellNavigator.ValueAsInt;
                // Get the navigator to the shared string value.  We only support
                // simple text strings.  All column headers and row headers will
                // always be unique so we don't check just assume that they are.
                XPathNavigator sharedStringValueNavigator =
                    sharedStringsNavigator.SelectSingleNode(
                        string.Format(
                            "//ss:si[position()={0}]/ss:t",
                            (sharedStringIndex + 1).ToString()),
                        nsMgr);
                sharedStringValueNavigator.SetValue(value);
            }
            else
            {
                // Move to the value node
                cellNavigator.MoveToChild("v", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                cellNavigator.SetValue(value);
            }

            return;
        }

        /// <summary>
        /// Get the letter & number designation for a cell.  The sheets dealt with in this template
        /// are small so we are only supporting one letter designations.
        /// </summary>
        /// <param name="row">int</param>
        /// <param name="col">int</param>
        /// <returns>string - The cell designation, i.e. A1 = (row 1, col 1), F8 = (row 8, col 6), etc.</returns>
        private static string GetCellAddress(int row, int col)
        {
            char colLetter = (char)(col + 64);
            return (colLetter + row.ToString());
        }
        #endregion

    #region "System.IO.Package Helpers"
    /// <summary>
    /// Get a PackagePart from the Package.  This is one of a small number of parts that
    /// have relationships defined at the root package level.
    /// </summary>
    /// <param name="officePackage">Package (Required)</param>
    /// <param name="partUrn">string (Required)</param>
    /// <returns>PackagePart</returns>
    private static PackagePart GetRootPart(Package officePackage, string partUrn)
    {
        PackagePart rootPart = null;

        foreach (PackageRelationship relationship in officePackage.GetRelationshipsByType(partUrn))
        {
            rootPart = ResolvePartRelationship(officePackage, relationship);

            // There should only be one of these types of part in the package
            break;
        }

        return rootPart;
    }

    /// <summary>
    /// Get the child PackagePart from the specified parent PackagePart
    /// for the specified urn.
    /// </summary>
    /// <param name="officePackage">Package (Required)</param>
    /// <param name="parentPart">PackagePart (Required)</param>
    /// <param name="partUrn">string (Required)</param>
    /// <returns>PackagePart</returns>
    private static PackagePart GetSinglePart(Package officePackage, PackagePart parentPart, string partUrn)
    {
        // Get the part
        PackagePart part = null;

        foreach (PackageRelationship relationship in parentPart.GetRelationshipsByType(partUrn))
        {
            part = ResolvePartRelationship(officePackage, relationship);

            // Get the first part only
            break;
        }

        return part;
    }

    /// <summary>
    /// Resolve a PackageRelationship to a PackagePart
    /// </summary>
    /// <param name="officePackage">Package (Required)</param>
    /// <param name="relationship">PackageRelationship (Required)</param>
    /// <returns>PackagePart</returns>
    private static PackagePart ResolvePartRelationship(Package officePackage, PackageRelationship relationship)
    {
        // Resolve the relationship
        Uri partUri =
            PackUriHelper.ResolvePartUri(relationship.SourceUri, relationship.TargetUri);
        PackagePart part = officePackage.GetPart(partUri);

        return part;
    }

    /// <summary>
    /// Get the custom xml datastore id
    /// </summary>
    /// <param name="officePackage">Package</param>
    /// <param name="customXmlPart">PackagePart</param>
    /// <returns>string</returns>
    private static string GetCustomXmlDatastoreId(Package officePackage, PackagePart customXmlPart)
    {
        PackagePart customXmlPropsPart =
            GetSinglePart(
                officePackage,
                customXmlPart,
                "http://schemas.openxmlformats.org/officeDocument/2006/relationships/customXmlProps");

        XPathDocument document = new XPathDocument(customXmlPropsPart.GetStream());
        XPathNavigator navigator = document.CreateNavigator();
        navigator.MoveToFirstChild();
        navigator.MoveToAttribute("itemID", "http://schemas.openxmlformats.org/officeDocument/2006/customXml");

        return navigator.Value;
    }

    /// <summary>
    /// Set the content value for the specified content area
    /// </summary>
    /// <param name="nsMgr">XmlNamespaceManager</param>
    /// <param name="navigator">XPathNavigator</param>
    /// <param name="contentAreaName">string</param>
    /// <param name="contentValue">string</param>
    private static void SetContentAreaContent(XmlNamespaceManager nsMgr, XPathNavigator navigator,
        string contentAreaName, string contentValue)
    {
        string nodeNamespace = nsMgr.LookupNamespace("w");
        string contentNodeXPath =
            string.Format(
                "//w:sdt[w:sdtPr/w:tag/attribute::w:val='{0}']/w:sdtContent",
                contentAreaName);
        string runNodeXPath = contentNodeXPath + "//w:r";
        XPathNavigator nodeNavigator = navigator.SelectSingleNode(contentNodeXPath, nsMgr);
        // Get rid of all but one of the <w:r> nodes
        while (nodeNavigator.SelectDescendants("r", nodeNamespace, false).Count > 1)
        {
            nodeNavigator.SelectSingleNode(runNodeXPath, nsMgr).DeleteSelf();
        }

        // Get the remaining <w:r> node
        XPathNavigator runNodeValueNode = nodeNavigator.SelectSingleNode(runNodeXPath + "/w:t", nsMgr);
        // Start the substring at index 1 so we get rid of the leading '@'
        runNodeValueNode.SetValue(contentValue.Substring(1));

        return;
    }

    /// <summary>
    /// Set the Xml binding path for the specified content area
    /// </summary>
    /// <param name="nsMgr">XmlNamespaceManager</param>
    /// <param name="navigator">XPathNavigator</param>
    /// <param name="datastoreId">string</param>
    /// <param name="contentAreaName">string</param>
    /// <param name="xpathValue">string</param>
    private static void SetContentAreaXmlMapping(XmlNamespaceManager nsMgr, XPathNavigator navigator,
        string datastoreId, string contentAreaName, string xpathValue)
    {
        string nodeNamespace = nsMgr.LookupNamespace("w");
        XPathNavigator nodeNavigator =
            navigator.SelectSingleNode(
                string.Format(
                    "//w:sdt/w:sdtPr[w:tag/attribute::w:val='{0}']",
                    contentAreaName),
                nsMgr);
        if (!nodeNavigator.MoveToChild(
                "dataBinding", nodeNamespace))
        {
            nodeNavigator.MoveToChild(
                "text", nodeNamespace);
            nodeNavigator.InsertElementBefore(
                "w",
                "dataBinding",
                nodeNamespace,
                null);
            nodeNavigator.MoveToPrevious();
            nodeNavigator.CreateAttribute("w", "xpath", nodeNamespace, xpathValue);
            nodeNavigator.CreateAttribute("w", "storeItemID", nodeNamespace, datastoreId);
        }
        else
        {
            nodeNavigator.MoveToAttribute("xpath", nodeNamespace);
            nodeNavigator.SetValue(xpathValue);
        }

        return;
    }
    #endregion


    internal struct ForecastData
    {
        public decimal Sales { get; set; }
        public int Probability { get; set; }
        public string FiscalYear {get; set; }
        public int Quarter { get; set; }
    }

}
